import pandas as pd
import os
import xpinyin
import json
from urllib.parse import urlsplit, parse_qsl
import re
from concurrent.futures import ThreadPoolExecutor

pwd = os.path.dirname(os.path.dirname(__file__))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'shop.settings')
import django

django.setup()

import snowflake.client
from bs4 import BeautifulSoup
import requests

headers = {
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36'
}
# headers.get('user-agent')


def get_snowflake_uuid():
    guid = snowflake.client.get_guid()
    return guid


def get_s(con):
    p = xpinyin.Pinyin()
    result = [p.get_initials(a, '') for a in [con]][0]
    return result


def get_name(rep):
    # url = f'https://npcitem.jd.hk/{sid}.html'
    # rep = requests.get(url, headers=headers)
    pattern = re.compile(r"name: '(.*?)'", re.MULTILINE | re.DOTALL)
    cont = re.findall(pattern, rep.content.decode())[0]
    return cont


def get_info(rep, sid):
    try:
        # url = f'https://npcitem.jd.hk/{sid}.html'
        pattern = re.compile(r"colorSize: \[(.*?)\], ", re.MULTILINE | re.DOTALL)
        cont = re.findall(pattern, rep.content.decode())[0]
        json_cont = json.loads('[' + cont + ']')
        # print(json_cont)
        filter_cont = [x for x in json_cont if x['skuId'] == int(sid)][0][list(json_cont[0].keys())[1]]

        # try:
        #     filter_cont = [x for x in json_cont if x['skuId'] == int(sid)][0]['规格']
        # except:
        #     filter_cont = [x for x in json_cont if x['skuId'] == int(sid)][0]['产品']
        return filter_cont
    except Exception as e:
        with open('cosme001.txt', 'a+', encoding='utf-8') as f:
            f.write(str(sid) + '\n')
        return ''


def get_content(sid, mid):
    url = f'https://cd.jd.com/description/channel?skuId={sid}&mainSkuId={mid}&charset=utf-8&cdn=2'
    rep = requests.get(url, headers=headers)
    return rep.json()['content'].replace('data-lazyload', 'src')


def get_id(url):
    # url = 'https://npcitem.jd.hk/27354256546.html'
    rep = requests.get(url, headers=headers)
    pattern = re.compile(r"desc: '(.*?)',", re.MULTILINE | re.DOTALL)
    cont = re.findall(pattern, rep.content.decode())[0]
    query = dict(parse_qsl(urlsplit(cont).query))
    skuId = query.get('skuId')
    mainSkuId = query.get('mainSkuId')
    content = get_content(skuId, mainSkuId)
    return content


def get_images(url):
    # url = 'https://npcitem.jd.hk/31497066274.html#crumb-wrap'
    rep = requests.get(url, headers=headers).content.decode()
    soup = BeautifulSoup(rep, 'lxml')
    imgs = ['https://img13.360buyimg.com/cms/' + x.get('data-url') for x in soup.select('#spec-list > ul > li > img')]
    return json.dumps(imgs)


def extract_url(url):
    # url = 'https://www.cosme-de.net/pd/products/ADN0141?product_no=ADN0215'
    split_url = urlsplit(url)
    path = split_url.path.split('/')[-1]
    query = dict(parse_qsl(split_url.query))['product_no']
    return path, query


from goods.models import Good, GoodCategory, GoodBrand, GoodTags, Source, Product, SPG


def gen_source():
    source = Source()
    source.name = 'cosme-de'
    source.url = 'https://www.cosme-de.net/'
    source.save()

    source = Source()
    source.name = 'petpochitto'
    source.url = 'https://www.petpochitto.com/'
    source.save()

    source = Source()
    source.name = 'superdelivery'
    source.url = 'https://www.superdelivery.com/'
    source.save()


def read_category1():
    # cate1
    df = pd.read_excel(r'cos-jd2.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate1_zh = row['cate1中文']

            # 一级
            s1 = GoodCategory.objects.filter(category_type=1).filter(name=cate1_zh)

            if s1.first():
                pass
            else:
                category1.name = cate1_zh
                category1.category_type = 1
                category1.parent_category = None
                category1.save()

        except Exception as e:
            with open('cosme-0527.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def read_category2():
    # cate1
    df = pd.read_excel(r'cos-jd2.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate1 = row['cate1中文']
            cate1_zh = row['cate2中文']

            # 二级  过滤
            s1 = GoodCategory.objects.filter(category_type=2).filter(name=cate1_zh)

            if s1.first():
                pass
            else:
                category1.name = cate1_zh
                category1.category_type = 2
                category1.parent_category = GoodCategory.objects.filter(category_type=1).get(name=cate1)
                category1.save()


        except Exception as e:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def read_category3():
    # cate1
    df = pd.read_excel(r'cos-jd2.xlsx')

    for item, row in df.iterrows():
        try:
            category1 = GoodCategory()
            cate1 = row['cate2中文']
            cate1_zh = row['cate3中文']

            # 三级
            s1 = GoodCategory.objects.filter(category_type=3).filter(name=cate1_zh)

            if s1.first():
                pass
            else:
                category1.name = cate1_zh
                category1.category_type = 3
                category1.parent_category = GoodCategory.objects.filter(category_type=2).get(name=cate1)
                category1.save()


        except Exception as e:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            print(e)
            continue


def brand():
    import random
    df = pd.read_excel(r'cos-jd2.xlsx')
    bs = [
        'https://kaola-haitao.oss.kaolacdn.com/ii02po4970_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ii02shtz66_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1c2gohbio65_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1cnij2nrr52_300_300.jpg',
        'http://kaola-haitao.oss.kaolacdn.com/ir1s6rzz67_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1bk8ndmup45_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1el9nv9cj24_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ifxc1wa478_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/1c126l96j31_300_300.jpg',
        'http://kaola-haitao.oss.kaolacdn.com/1btlvtbrr35_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ieww1xa027_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ig28fghh45_300_300.jpg',
        'https://kaola-haitao.oss.kaolacdn.com/ix2kwjyf95_300_300.jpg'
    ]
    for item, row in df.iterrows():
        try:
            cate = row['cate1中文']
            en = row['en_brand_y']
            jp = row['中文品牌名']
            b1 = GoodBrand.objects.filter(en_name=en).first()
            if b1:
                pass
            else:
                brand = GoodBrand()
                brand.category = GoodCategory.objects.filter(category_type=1).get(name=cate)
                brand.zh_name = jp
                brand.en_name = en
                brand.letter = str(en)[:1].upper() if len(str(en)) > 1 else '#'
                brand.image = random.choice(bs)
                brand.save()
        except:
            with open('cosme_brand.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def task(row):
    try:
        # 需要加锁
        # (1062, "Duplicate entry 'ADN0141' for key 'goods_product_third_sn_80b601a7_uniq'")
        cate = row['cate3中文']
        en = row['en_brand_y']
        url = row['url']
        zh = row['中文品牌名']
        path, query = extract_url(url)
        real_url = row['映射网址']
        if Product.objects.filter(third_sn=path):
            pass
        else:
            sid = urlsplit(real_url).path.strip('/.html')
            rep = requests.get(real_url, headers=headers)
            attr = get_info(rep, sid)
            print(attr)
            good = Product()

            good.source = Source.objects.get(name='cosme-de')
            # bug 修复  控制分类范围
            good.category = GoodCategory.objects.filter(category_type=3).get(name=cate)
            good.brand = GoodBrand.objects.get(en_name=en)
            good.third_sn = path
            good.title = get_name(rep).rstrip(attr)
            good.sn = get_snowflake_uuid()
            good.detail = get_id(real_url)
            good.images = get_images(real_url)
            good.data_url = real_url
            good.source_url = url
            good.save()

    except Exception as e:
        url = row['url']
        print(e)
        with open('cosme.txt', 'a+', encoding='utf-8') as f:
            f.write(str(url) + '\n')
        # continue


def good():
    df = pd.read_excel(r'cos-jd3.xlsx')
    with ThreadPoolExecutor(10) as execute:
        for _, row in df.iterrows():
            execute.submit(task, row)


from hashlib import md5


def request_params_md5(p):
    m = md5()
    m.update(str(p).encode('utf-8'))
    return m.hexdigest()


def good_tag():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            cate = row['pid_x']
            good = Good.objects.get(sn=cate)
            tags = eval(row['tags_x'])
            for tag in tags:
                l = GoodTags.objects.get(jp_name=tag)
                good.tag = good.tag.add(l)
                good.save()

        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good_param():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            pid = row['pid_x']
            sku = eval(row['skus_y'])
            url = row['url']
            kk = Good.objects.get(sn=pid)
            for sk in sku:
                # print(sk)
                param = GoodParam()
                param.be_good = kk
                param.classify = sk['attr_name']
                param.sku_id = request_params_md5(sk['sku'] + url)
                param.good_model = ''
                param.status = 1 if sk['status'] == 'InStock' else 0
                param.cover = sk['image'][0]
                param.shop_price = sk['sold_price']
                param.market_price = sk['market_price']
                param.save()


        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def tag():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            tags = eval(row['tags_x'])
            for item in tags:
                t = GoodTags.objects.filter(jp_name=item).first()
                if t:
                    pass
                else:
                    tag = GoodTags()
                    tag.en_name = ''
                    tag.jp_name = item
                    tag.save()
        except:
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


def good_image():
    df = pd.read_excel(r'cos22.xlsx')

    for item, row in df.iterrows():
        try:
            pid = row['pid_x']
            sku = eval(row['skus_y'])
            kk = Good.objects.get(sn=pid)
            # print(sku)
            for goods_image in sku:
                imgs = goods_image['image']
                for img in imgs:
                    goods_image_instance = GoodCarouselImage()
                    goods_image_instance.image = img
                    goods_image_instance.good = kk
                    goods_image_instance.save()

        except Exception as e:
            print(e)
            with open('cosme.txt', 'a+', encoding='utf-8') as f:
                f.write(str(item) + '\n')
            continue


if __name__ == '__main__':
    # read_category3()
    # brand()
    # gen_source()
    good()
